{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 作业3 空间数据库设计"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "姓名：3180102760"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "学号：郑昱笙"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**作业目的：**了解数据库设计基本流程和设计异常，了解空间数据的获取方式，熟悉空间扩展E/R图，掌握E/R图与关系的转化，掌握关系数据库规范化设计方法，掌握数据的ETL过程（数据抽取Extract、清洗Clean、转换Transform、转载Load）。\n",
    "\n",
    "**注意事项：**\n",
    "* SQL语句的错误输出为乱码时，修改SET client_encoding = 'GBK';或SET client_encoding = 'UTF-8';，重新连接数据库\n",
    "* Jupyter Notebook对SQL语句的错误提示较弱，可以先在pgAdmin 4上执行，查看详细的错误信息\n",
    "* 作业3总分40分，作业考察的题目后面标了具体分数，可以相互讨论思路，作业抄袭或雷同都要扣分\n",
    "* **学号.jpg和作业3\\_学号\\_姓名.ipynb**替换其中的学号和姓名，包含执行结果，一起压缩为__作业3\\_学号\\_姓名.rar/zip__，提交到学在浙大，截止日期**2020.4.12**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. 空间数据应用（2分）\n",
    "\n",
    "[Geospatial Revolution](http://geospatialrevolution.psu.edu/)是关于地理空间革命的科普视频，共4集，每集内容描述如下：\n",
    "* Episode 1: covers what is involved in the geospatial revolution, the origins of mapping and geospatial technology, and a look at the use of crisis mapping in Haitian earthquake relief efforts.\n",
    "* Episode 2: looks at how local governments and business use geospatial technology to deliver services and run efficiently, keeping a continuing eye on future developments and applications.\n",
    "* Episode 3: explores geospatial technology in the world of security: how new technologies help to broker peace, wage war, and fight crime but can also compromise personal privacy.\n",
    "* Episode 4: explores geospatial technology around the world: monitoring global climate change, preventing famine, tracking disease and mapping communities never before seen on a map.\n",
    "\n",
    "观看Geospatial Revolution Episode 2，[Bilibili链接](https://www.bilibili.com/video/av10498825/)，从中选择一个你最感兴趣的应用，请用1-2句话描述选择的应用，并给出可能需要哪些地理空间数据？（2分）"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "应用：使用手机地图或点评软件搜索附近评分最高的餐厅；\n",
    "需要地理空间数据：使用者当前所处的位置、餐厅的地理位置和评分数据，还可能需要到达餐厅所需要的路线数据来计算实际需要通过的距离；"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. 空间数据库设计（20分）\n",
    "\n",
    "美食外卖校园创业公司“吃了么”正在架构后台服务器，CTO得知你正在学习空间数据库，想聘请你作为数据库首席架构师，构建美食外卖应用软件所需的空间数据库。在和CTO沟通后，你发现公司初步选择PostgreSQL + PostGIS作为数据库服务器，并获得了以下需求：\n",
    "\n",
    "* 数据库需要记录每个客户(**customer**)的姓名(customter name)，手机号(phone)和配送地址(address)。\n",
    "* 客户可以根据餐厅的名称或当前位置搜索(search)附近的餐厅(**restaurant**)，每个餐厅信息包含有餐厅名(restaurant name)，电话(telephone)和位置(location)，在选择餐厅后，客户可以进一步完成下单操作(place an **order**)。\n",
    "* 餐厅每天产生多个订单，每个订单包含唯一的订单编号(order id)及其对应的订单内容(details)。餐厅将这些订单分配(assign)给多个快递员(**courier**)。\n",
    "* 数据库需要记录每个快递员的快递证号(licence)，姓名(name)，手机号(phone)和车辆信息(vehicle)，快递员可以查看(view)系统分配给自己的订单信息。\n",
    "* 在配送过程中，快递员可以设置(operate)自己能够配送的区域(**delivery zone**)，每个区域具有唯一的区域编号(zone id)与对应的区域信息(area)。\n",
    "* 每个区域包含(consist)多条配送路径(**route**)，每条路径有起点(source)、终点(destination)、预计配送时间(expected travel time)和道路信息(road segments)，路径能够达到(direct)客户所指定的配送地址。\n",
    "\n",
    "基于上述需求分析和现实世界语义，构建空间扩展E/R图，分析每个实体中存在的完全非平凡函数依赖，并转化为数据库关系，建议使用上述英文单词作为实体/联系/属性名。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.1 实体分析（5分）\n",
    "基于需求分析，描述实体的属性和完全非平凡的函数依赖，指定关系的主码，说明哪些属性是空间属性，给出其最恰当的几何类型。"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "1. Customer实体\n",
    "属性：id, name, phone, address，position，主码是id，空间属性是position,几何类型是Point\n",
    "函数依赖：\n",
    "id --> phone, name, address, position\n",
    "分配的id可以唯一确定其他属性；\n",
    "phone --> id, name, address, position\n",
    "通常使用电话号码来进行账号注册和身份绑定，保证通过电话号码可以唯一确定用户的身份；\n",
    "\n",
    "2. Restaurant实体\n",
    "属性：id, name, phone, location, 主码是id，空间属性是location，几何类型是Point\n",
    "函数依赖：\n",
    "id --> name, phone, location\n",
    "分配的id可以唯一确定其他属性；\n",
    "phone --> id, name, location\n",
    "通常使用电话号码来进行账号注册和身份绑定，保证通过电话号码可以唯一确定商家的身份；\n",
    "location --> name, phone,id\n",
    "每个商家的地理位置都是唯一的，可通过地址唯一确定一个商家店面；\n",
    "\n",
    "3. Order实体\n",
    "属性：id, details, 主码是id，无空间属性\n",
    "id --> details\n",
    "分配的id可以唯一确定其他属性；\n",
    "\n",
    "4. Courier实体\n",
    "属性：licence, name, phone, vehicle, 主码是licence，无空间属性\n",
    "licence -->  name, phone, vehicle\n",
    "分配的licence可以唯一确定其他属性；\n",
    "phone --> licence, name, vehicle\n",
    "通常使用电话号码来进行账号注册和身份绑定，保证通过电话号码可以唯一确定快递员的身份；\n",
    "\n",
    "5. Delivery zone实体\n",
    "属性：id, area, 主码是id，空间属性是area，几何类型是Polygon\n",
    "id --> area\n",
    "area --> id\n",
    "分配的id可以唯一确定其他属性；\n",
    "每个区域的范围都不相同，范围可唯一确定一个区域；\n",
    "\n",
    "6. Route实体\n",
    "属性：id, source, destination, expected travel time, road segments,  主码是id, 空间属性是source, destination, road segments, 几何类型分别是Point、Point、Line\n",
    "id --> source, destination, expected travel time, road segments\n",
    "分配的id可以唯一确定其他属性；\n",
    "road segments --> id, source, destination, expected travel time\n",
    "每个道路的路径都不相同，路径可唯一确定一个道路；"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.2 联系分析（6分）\n",
    "基于2.1的实体，分析实体之间存在哪些联系，并指定联系的类型。"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Custumer和Restaurant实体之间存在Search联系，联系类型是1:N（客户一次搜索获得多个餐厅）\n",
    "Custumer和Order实体之间存在Place联系，联系类型是1:N（客户一次可产生多个订单）\n",
    "Restaurant和Order实体之间存在Assign联系，联系类型是1:N（餐厅一次可获得并分配多个订单）\n",
    "Courier和Order实体之间存在View联系，联系类型是1:N（快递员一次可被分配多个订单）\n",
    "Courier和delivery zone实体之间存在operate联系，联系类型是M:N（快递员一次设置多个配送区域，每个配送区域可由多人配送）\n",
    "delivery zone和Route实体之间存在consist联系，联系类型是M:N（每个区域可以包含多个路径,每个路径可以穿越多个区域）\n",
    "Custumer和Route实体之间存在direct联系，联系类型是1:N（一个客户可以由多个路径到达）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.3 空间扩展E/R图（3分）\n",
    "\n",
    "空间扩展E/R图可以使用软件或手工绘图（如下图），空间属性使用实体象形图进行标注，联系需给出联系类型（1:N标注或箭头表示），截图或照片保存为学号.png，与本文件同一目录，修改下面的ER.png为你的学号.png，Shift+Enter能正确展示空间扩展E/R图（如果浏览器缓存数据，请重启Kernel查询空间扩展E/R图）。\n",
    "\n",
    "<img src=\"3180102760.png\">"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.4 关系转换（4分）\n",
    "\n",
    "将空间扩展E/R图转换为关系，标注几何属性的几何类型，如geom(Point)，说明关系的主码和外码，联系关系尽可能和实体关系合并，但关系都需属于BCNF。"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Customer(id primary key, name, phone, address, position geom(Point))\n",
    "Restaurant(id primary key, name, location geom(Point))\n",
    "Search(Customer id references Customer(id), Restaurant id references Restaurant(id),  primary key(Customer id, Restaurant id))\n",
    "Order(id primary key, details, customer references Customer(id), restaurant references Restaurant(id), courier references Courier(licence))\n",
    "Courier(licence primary key, name, phone, vehicle)\n",
    "OperateZone(Courier licenc references Courier(licence), zone id references DeliveryZone(id), primary key(Courier licence, zone id))\n",
    "DeliveryZone(id primary key, area geom(Polygon))\n",
    "Route(id, source geom(Point), destination geom(Point), expected travel time, road segments geom(Line), \n",
    "directed customer references Customer(id))\n",
    "Consist(route id references Route(id), zone id references DeliveryZone(id), primary key(route id,zone id))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.5 数据库合并（2分）\n",
    "\n",
    "经过数据库设计的不断优化，‘吃了吗’用户不断增加，公司计划收购另一家外卖公司‘饱了吗’，需要集成两家公司的数据库。作为数据库首席架构师，你需要在收购筹备会上提出两家公司的数据库集成可能面临的挑战。请至少给出两个挑战，每个挑战需要具体举例说明。"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "1. 数据库异构性：例如‘吃了吗’使用对象关系数据库（关系模型）PostgreSQL存储数据，而‘饱了吗’使用半结构化数据库(文档模型)MongoDB存储数据。\n",
    "2. 数据库分布性：数据库在合并和传输集成过程中，需要保证传输的高效性、可靠性、安全性，例如两家公司的大量数据分布在两地的两个不同数据中心，需要通过网络进行传输，要保证传输过程中不会出现差错、尽可能不影响用户正常使用、数据不被盗等。\n",
    "3. 数据库语义异构性：两个数据库可能采用不同的业务逻辑，建立了不同的实体关系模型，字段间语义表示可能也不相同，例如‘吃了吗’将快递员的配送范围与配送区域进行绑定，为每个区域记录配送路径；而‘饱了吗’将快递员的配送范围直接与客户进行绑定，为每个客户分配特定的外送员，为客户和餐厅之间建立配送路径关系，让路径附属于餐厅。\n",
    "4. 数据库的自治性：每个数据库可能对自身结构和数据进行一定程度上的改变，如‘吃了吗’可能需要在疫情期间为一定配送范围内的客户建立自提点，需要增加相关的关系来记录自提点数据，并改变配送路径的终点；而‘饱了吗’让用户自行决定配送目的地。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 2.6（附加题）扩展需求\n",
    "上述基本需求没有考虑时间因素，例如订单有下单时间，快递员有实时位置等，增加时间属性，重新绘制E/R图，并转换为关系，需满足BCNF。根据设计合理性，最高奖励4分。\n",
    "\n",
    "E/R图\n",
    "<img src='3180102760_ex.png'>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "说明：\n",
    "订单有下单时间、到达时间；\n",
    "快递员有实时位置；\n",
    "商家可以有开始营业时间，结束营业时间；"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "关系：\n",
    "Customer(id primary key, name, phone, address, position geom(Point))\n",
    "Restaurant(id primary key, name, openTime, closeTime, location geom(Point))\n",
    "Search(Customer id references Customer(id), Restaurant id references Restaurant(id),  primary key(Customer id, Restaurant id))\n",
    "Order(id primary key, details, placed time, finish time,  customer references Customer(id), restaurant references Restaurant(id), courier references Courier(licence))\n",
    "Courier(licence primary key, name, phone, vehicle, position geom(Point))\n",
    "OperateZone(Courier licenc references Courier(licence), zone id references DeliveryZone(id), primary key(Courier licence, zone id))\n",
    "DeliveryZone(id primary key, area geom(Polygon))\n",
    "Route(id, source geom(Point), destination geom(Point), expected travel time, road segments geom(Line), \n",
    "directed customer references Customer(id))\n",
    "Consist(route id references Route(id), zone id references DeliveryZone(id), primary key(route id,zone id))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. BCNF（7分）\n",
    "\n",
    "假设关系R(A, B, C, D, E)有如下函数依赖：AB  $\\rightarrow$ C, BC  $\\rightarrow$ D, CD  $\\rightarrow$ E, DE  $\\rightarrow$ A。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.1 关系R所有的Keys。（2分）"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "AB\n",
    "BC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.2 将关系R分解为BCNF，描述具体的分解步骤，说明分解后关系的主码，以及是否有丢失函数依赖。（3分）"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "选择DE->A, 将R分解为:\n",
    "    R1(DEA), 主码为DE\n",
    "    R2(BCDE), 主码为BC，函数依赖为CD->E、BC->D，不满足BCNF，选择CD->E,将R2分解为：\n",
    "       R21(CDE), 主码为CD\n",
    "       R22(BCD)，主码为BC，函数依赖BC->D, 满足BCNF\n",
    "总共分解为：\n",
    "R1(DEA), 主码为DE，函数依赖DE->A\n",
    "R21(CDE), 主码为CD，函数依赖CD->E\n",
    "R22(BCD), 主码为BC，函数依赖BC->D\n",
    "丢失函数依赖：AB->C"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3.3 是否存在不同的分解？如果存在，给出不同的分解，描述具体的分解步骤，说明分解后关系的主码，以及是否有丢失函数依赖。。（2分"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "存在。\n",
    "选择CD->E, 将R分解为:\n",
    "    R1(CDE), 主码为CD\n",
    "    R2(ABCD), 主码为AB，函数依赖为AB->C、BC->D，不满足BCNF，选择BC->D,将R2分解为：\n",
    "       R21(BCD), 主码为BC\n",
    "       R22(ABC)，主码为AB，函数依赖AB->C, 满足BCNF\n",
    "总共分解为：\n",
    "R1(CDE), 主码为CD，函数依赖CD->E\n",
    "R21(BCD), 主码为BC，函数依赖BC->D\n",
    "R22(ABC)，主码为AB，函数依赖AB->C\n",
    "丢失函数依赖：DE->A"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. 数据库逆向设计（11分）\n",
    "礼品店的李老板设计了一个简单的数据库，用来记录礼品店每月的销售记录。由于没有学过数据库，他设计的数据库模式为Sales(name, discount, month, price)。他将已有的数据插入到Sales关系中。在之后的运行中，李老板发现这一数据库模式不太好用。\n",
    "\n",
    "李老板得知你正在学习数据库，想请你来帮助他解决礼品店数据管理问题，即规范化关系。他给出了之前的销售记录SaleData.txt，但不幸的是，李老板正好要出差，没法和你面对面交流，找出礼品店销售中存在的函数依赖（即无法和客户进行需求分析）。所以，你只能逆向工程，从给出的数据中寻找函数依赖。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.1 创建Sales表，载入数据"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过pgAdmin 4在PostgreSQL中创建hw3数据库，并连接该数据库。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql postgresql://postgres:postgres@localhost:5432/hw3\n",
    "\n",
    "SET statement_timeout = 0;\n",
    "SET lock_timeout = 0;\n",
    "SET client_encoding = 'utf-8';\n",
    "SET standard_conforming_strings = on;\n",
    "SET check_function_bodies = false;\n",
    "SET client_min_messages = warning;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "drop table if exists Sales;\n",
    "create table Sales(\n",
    "    name text,\n",
    "    discount text,\n",
    "    month text,\n",
    "    price integer\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "426 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "copy Sales from  'D://works//GIS//hw3//SaleData.txt';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.2 找出数据中存在的所有完全非平凡函数依赖（5分）\n",
    "\n",
    "由于无法了解客户的需求，只能通过试错法不断尝试，从最简单的函数依赖开始，如name $\\rightarrow$ discount，即当记录的name属性相同时，对应的discount属性也相同，然后再尝试复杂的函数依赖，如name, discount $\\rightarrow$ month。对于每个函数依赖，写一个SQL语句进行检查，要求SQL语句尽可能简短，且能通过查询结果是否是空集，确定是否存在所查找的函数依赖（不是看数据内容，人工判断确定）。\n",
    "\n",
    "Be Clever，结合函数依赖的规则减少需要检查的函数依赖数目，同时不能丢失任何可能的函数依赖。\n",
    "\n",
    "构造一个SQL查询语句，检查是否存在A $\\rightarrow$ B类型的完全非平凡函数依赖，其中A和B可以是Sales中的任何属性（从Sales关系中选择两个属性，使用下面SQL语句检查是否存在这一函数依赖）。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>FD name-&gt;price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>True</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(True,)]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "select count(*) = 0 as \"FD name->price\" from Sales s1,Sales s2 where s2.name = s1.name and s2.price != s1.price;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "构造一个SQL查询语句，检查是否存在A, B $\\rightarrow$ C类型的完全非平凡函数依赖，其中A、B和C可以是Sales中的任何属性（从Sales关系中选三个属性，使用下面SQL语句检查是否存在这一函数依赖）。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>FD name, month-&gt;dicount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>True</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(True,)]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select count(*)=0 as \"FD name, month->dicount\" from Sales s1,Sales s2 where s2.name = s1.name and s2.month = s1.month and s2.discount != s1.discount;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过上述过程的查找，给出Sales关系中存在的最小**完全非平凡**函数依赖集合，即a minimal set of completely nontrivial FDs such that all FDs that hold on the relation follow from the dependencies in the set，不在该集合中的函数依赖，都可以通过集合中的函数依赖根据规则推导获得。单属性之间的判断可以逐个写SQL语句判断，也可以用修改以下Python代码自动判断所有单属性依赖。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4620 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "name->price\n",
      "\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "3286 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "14700 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "13208 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "month->discount\n",
      "\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "17906 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "21034 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "14964 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "61398 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "48032 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "55170 rows affected.\n",
      "12\n"
     ]
    }
   ],
   "source": [
    "colums = ['name','month','price','discount']\n",
    "template1 = 'select * from Sales s1,Sales s2 where s2.%s = s1.%s and s2.%s != s1.%s' # 使用%作为占位符，类似于C语言的%s\n",
    "\n",
    "count = 0\n",
    "for A in colums:\n",
    "    for B in colums:\n",
    "        if A != B:\n",
    "            #print(A+\" \"+B)\n",
    "            query = template1 % (A, A, B, B)\n",
    "            result = %sql $query\n",
    "            count = count+1\n",
    "            if len(result) == 0:\n",
    "                print(A + '->' + B)\n",
    "                print()\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4620 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "3286 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "1334 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "1492 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "14700 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "13208 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "6228 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "6070 rows affected.\n",
      "8\n"
     ]
    }
   ],
   "source": [
    "template2 = 'select * from Sales s1,Sales s2 where s2.%s = s1.%s and s2.%s = s1.%s and s2.%s != s1.%s' # 使用%作为占位符，类似于C语言的%s\n",
    "colums = ['name','month','price','discount']\n",
    "\n",
    "count = 0\n",
    "\n",
    "for i in range(0,4):\n",
    "    for j in range(i,4):\n",
    "        for k in range(0,4):\n",
    "            A = colums[i]\n",
    "            B = colums[j]\n",
    "            C = colums[k]\n",
    "            if A != B and B!=C and A!=C \\\n",
    "            and (not ((A =='name' or B =='name') and C == 'price'))\\\n",
    "            and (not ((A =='month' or B =='month') and C == 'discount')):\n",
    "                #print(A+\" \"+B+\" \"+ C)\n",
    "                query = template2 % (A, A, B, B, C, C)\n",
    "                result = %sql $query\n",
    "                count = count+1\n",
    "                if len(result) == 0:\n",
    "                    print(A +\" \"+B+ '->' + C)\n",
    "                    print()\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name price discount month\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "1334 rows affected.\n",
      "month price discount name\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "1492 rows affected.\n",
      "2\n"
     ]
    }
   ],
   "source": [
    "template2 = 'select * from Sales s1,Sales s2 where s2.%s = s1.%s and s2.%s = s1.%s and s2.%s = s1.%s and s2.%s != s1.%s' # 使用%作为占位符，类似于C语言的%s\n",
    "colums = ['name','month','price','discount']\n",
    "\n",
    "count = 0\n",
    "\n",
    "for i in range(0,4):\n",
    "    for j in range(i,4):\n",
    "        for k in range(j,4):\n",
    "            for h in range(0,4):\n",
    "                A = colums[i]\n",
    "                B = colums[j]\n",
    "                C = colums[k]\n",
    "                D = colums[h]\n",
    "                if A != B and B!=C and A!=C and  A != D and B!=D and C!=D\\\n",
    "                and (not ((A =='name' or B =='name' or C =='name') and D == 'price'))\\\n",
    "                and (not ((A =='month' or B =='month'or C =='month') and D == 'discount')):\n",
    "                    print(A+\" \"+B+\" \"+ C+\" \"+D)\n",
    "                    query = template2 % (A, A, B, B, C, C, D, D)\n",
    "                    result = %sql $query\n",
    "                    count = count+1\n",
    "                    if len(result) == 0:\n",
    "                        print(A +\" \"+B+\" \"+C+ '->' + D)\n",
    "                        print()\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "最小完全非平凡函数依赖集合：\n",
    "name->price\n",
    "month->discount"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对于Sales关系，获得上述最小完全非平凡函数依赖集合至少需要多少次SQL查询？三个属性之间的函数依赖(A, B $\\rightarrow$ C)可以利用两个属性之间的函数依赖(A $\\rightarrow$ B)集合减少SQL查询次数。"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "第一次查询A → B函数依赖类型需要12次；\n",
    "利用两个属性间的函数依赖，并去除序列重复之后可得：\n",
    "还需要查询8+2 = 10次"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.3 异常分析（2分）\n",
    "\n",
    "基于发现的函数依赖，分析Sales关系设计存在哪些设计异常，并给出具体理由？"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "1、数据冗余：\n",
    "    name和对应的price，以及month和对应的discount分别存储了多次；\n",
    "2、更新异常：\n",
    "   当商品更改名称时，需要对多个与之相关的销售记录进行更改；\n",
    "3、插入异常：\n",
    "   新增商品但无商品销售记录时，无法在数据库中记录该商品；\n",
    "4、删除异常：\n",
    "   若某个商品只售出了一次，需要删除该销售记录的同时也会把对应商品的信息删除；"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.4 BCNF分解（2分）\n",
    "\n",
    "基于发现的函数依赖，使用BCNF分解算法，将Sales表分解为满足BCNF的关系，描述具体的分解步骤，说明分解后关系的主码，以及是否有丢失函数依赖。"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "Sales(name, discount, month, price)，选择函数依赖：name->price，可分解为\n",
    "    SalesItems(name, price),主码是name\n",
    "    SalesR(name，month, discount), 主码是name, month, 不满足BCNF；\n",
    "       选择函数依赖: month->discount，可分解为：\n",
    "           SalesDiscount(month, discount),主码是month\n",
    "           SalesRecord(name,month), 主码是name和month\n",
    "没有丢失函数依赖：共分解为三个关系\n",
    "SalesItems(name, price),主码是name，函数依赖：name->price\n",
    "SalesDiscount(month, discount),主码是month，函数依赖: month->discount\n",
    "SalesRecord(name,month), 主码是name和month"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.5 数据ETL（2分）\n",
    "\n",
    "创建分解后的关系，指定主码和外码，将Sales表中的数据，通过SQL插入和查询语句导入到分解后的关系中。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists SalesItems CASCADE;\n",
    "drop table if exists SalesDiscount CASCADE;\n",
    "drop table if exists SalesRecord;\n",
    "create table SalesItems(\n",
    "        name text primary key,\n",
    "        price integer\n",
    "    );\n",
    "create table SalesDiscount(\n",
    "    month text  primary key,\n",
    "    discount text\n",
    "    );\n",
    "create table SalesRecord(\n",
    "        name text references SalesItems(name),\n",
    "        month text references SalesDiscount(month),\n",
    "        primary key(name,month)\n",
    "    );"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "36 rows affected.\n",
      "12 rows affected.\n",
      "426 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "insert into SalesItems (select distinct name, price from Sales);\n",
    "insert into SalesDiscount(select distinct month, discount from Sales);\n",
    "insert into SalesRecord(select name, month from Sales);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过compare_result检查BCNF分解是否是join lossless？"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "426 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "426 rows affected.\n",
      "True\n"
     ]
    }
   ],
   "source": [
    "from relation_algebra import BaseRelation, compare_results\n",
    "X = %sql select SalesItems.name, SalesDiscount.month, discount, price  from SalesItems, SalesRecord, SalesDiscount where SalesItems.name = SalesRecord.name and SalesRecord.month = SalesDiscount.month;\n",
    "x = BaseRelation(X)\n",
    "\n",
    "Y = %sql select * from Sales\n",
    "y = BaseRelation(Y)\n",
    "\n",
    "print(compare_results(x, y))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "关系数据库设计的常见分析流程如下：\n",
    "* 列出你设计的所有关系，为每个关系指定主码。<br/>\n",
    "* 列出你设计的关系中的所有完全非平凡函数依赖，也可以回答“没有”。<br/>\n",
    "* 你设计的关系是否属于Boyce-Codd Normal Form (BCNF)范式？如果不是，请重新设计，或者解释为什么采用非BCNF较好。<br/>\n",
    "* 列出你设计的关系中的所有非平凡多值依赖，也可以回答“没有”。<br/>\n",
    "* 你设计的关系是否属于第4范式？如果不是，请重新设计，或者解释为什么采用非4-NF较好。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### 5. 多值依赖（附加题+练习题）\n",
    "\n",
    "5.1（附加题）关系R(A, B, C, D)的属性均无NULL值，构造一个SQL语句判断关系R的数据是否存在多值依赖B  $\\twoheadrightarrow$ AC。\n",
    "\n",
    "构造关系实例，进行验证，至少一个正例和一个负例。当存在B $\\twoheadrightarrow$ AC时，SQL语句返回空集，不存在时，SQL语句返回违背该多值依赖的元组。（2分）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists R;\n",
    "create table R(A int, B int, C int, D int);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "构造一个多值依赖B $\\twoheadrightarrow$ AC成立的关系实例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "delete from R;\n",
    "insert into R values (2, 1, 3, 4);\n",
    "insert into R values (2, 1, 3, 5);\n",
    "insert into R values (8, 1, 9, 4);\n",
    "insert into R values (8, 1, 9, 5);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "构造SQL语句查询多值依赖B $\\twoheadrightarrow$ AC是否成立"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>?column?</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>True</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(True,)]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select count(*) = 16 from\n",
    "(select r1.A,r1.B,r1.C,r1.D, r2.A,r2.B,r2.C,r2.D, count(*) from R r1, R r2, R r3, R r4 where \n",
    "r1.B = r2.B and r3.B = r2.B and r3.B = r4.B and \n",
    "r3.A = r1.A and R3.C = r1.C and\n",
    "r4.A = r2.A and R4.C = r2.C and\n",
    "r3.D = r2.D and r4.D = r1.D \n",
    "group by r1.A,r1.B,r1.C,r1.D, r2.A,r2.B,r2.C,r2.D) result;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "构造一个多值依赖B $\\twoheadrightarrow$ AC不成立的关系实例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "delete from R;\n",
    "insert into R values (2, 1, 3, 4);\n",
    "insert into R values (2, 1, 3, 5);\n",
    "insert into R values (8, 1, 9, 6);\n",
    "insert into R values (8, 1, 9, 7);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "拷贝SQL语句查询多值依赖B $\\twoheadrightarrow$ AC是否成立"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>?column?</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>False</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(False,)]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "select count(*) = 16 from\n",
    "(select r1.A,r1.B,r1.C,r1.D, r2.A,r2.B,r2.C,r2.D, count(*) from R r1, R r2, R r3, R r4 where \n",
    "r1.B = r2.B and r3.B = r2.B and r3.B = r4.B and \n",
    "r3.A = r1.A and R3.C = r1.C and\n",
    "r4.A = r2.A and R4.C = r2.C and\n",
    "r3.D = r2.D and r4.D = r1.D \n",
    "group by r1.A,r1.B,r1.C,r1.D, r2.A,r2.B,r2.C,r2.D) result;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.2（附加题）关系R(A, B, C, D)**仅**存在两个函数依赖：AC $\\rightarrow$ B和D $\\rightarrow$ C。构造关系实例，存在上述函数依赖，但不存在其他函数依赖。（2分）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "Done.\n",
      "Done.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "drop table if exists R;\n",
    "create table R(A int, B int, C int, D int);\n",
    "\n",
    "insert into R values (2, 1, 3, 4);\n",
    "insert into R values (3, 1, 3, 4);\n",
    "insert into R values (4, 3, 2, 5);\n",
    "insert into R values (2, 2, 2, 5);\n",
    "insert into R values (2, 2, 2, 7);\n",
    "insert into R values (3, 1, 2, 6);\n",
    "insert into R values (5, 3, 2, 6);"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "以下构造查询证明仅存在AC->B和D->C"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "6 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "8 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "6 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "8 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "20 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "16 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "16 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "6 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "D->C\n",
      "\n",
      "12\n"
     ]
    }
   ],
   "source": [
    "colums = ['A','B','C','D']\n",
    "template1 = 'select * from R s1,R s2 where s2.%s = s1.%s and s2.%s != s1.%s' # 使用%作为占位符，类似于C语言的%s\n",
    "\n",
    "count = 0\n",
    "for A in colums:\n",
    "    for B in colums:\n",
    "        if A != B:\n",
    "            #print(A+\" \"+B)\n",
    "            query = template1 % (A, A, B, B)\n",
    "            result = %sql $query\n",
    "            count = count+1\n",
    "            if len(result) == 0:\n",
    "                print(A + '->' + B)\n",
    "                print()\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "2 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "A C->B\n",
      "\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "2 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "A D->B\n",
      "\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "A D->C\n",
      "\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "2 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "B D->C\n",
      "\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "6 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "4 rows affected.\n",
      "12\n"
     ]
    }
   ],
   "source": [
    "template2 = 'select * from R s1,R s2 where s2.%s = s1.%s and s2.%s = s1.%s and s2.%s != s1.%s' # 使用%作为占位符，类似于C语言的%s\n",
    "colums = ['A','B','C','D']\n",
    "\n",
    "count = 0\n",
    "\n",
    "for i in range(0,4):\n",
    "    for j in range(i+1,4):\n",
    "        for k in range(0,4):\n",
    "            A = colums[i]\n",
    "            B = colums[j]\n",
    "            C = colums[k]\n",
    "            if A != B and B!=C and A!=C:\n",
    "                #print(A+\" \"+B+\" \"+ C)\n",
    "                query = template2 % (A, A, B, B, C, C)\n",
    "                result = %sql $query\n",
    "                count = count+1\n",
    "                if len(result) == 0:\n",
    "                    print(A +\" \"+B+ '->' + C)\n",
    "                    print()\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "2 rows affected.\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "A B D->C\n",
      "\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "0 rows affected.\n",
      "A C D->B\n",
      "\n",
      " * postgresql://postgres:***@localhost:5432/hw3\n",
      "2 rows affected.\n",
      "4\n"
     ]
    }
   ],
   "source": [
    "template2 = 'select * from R s1,R s2 where s2.%s = s1.%s and s2.%s = s1.%s and s2.%s = s1.%s and s2.%s != s1.%s' # 使用%作为占位符，类似于C语言的%s\n",
    "colums = ['A','B','C','D']\n",
    "\n",
    "count = 0\n",
    "\n",
    "for i in range(0,4):\n",
    "    for j in range(i,4):\n",
    "        for k in range(j,4):\n",
    "            for h in range(0,4):\n",
    "                A = colums[i]\n",
    "                B = colums[j]\n",
    "                C = colums[k]\n",
    "                D = colums[h]\n",
    "                if A != B and B!=C and A!=C and  A != D and B!=D and C!=D:\n",
    "                    #print(A+\" \"+B+\" \"+ C+\" \"+D)\n",
    "                    query = template2 % (A, A, B, B, C, C, D, D)\n",
    "                    result = %sql $query\n",
    "                    count = count+1\n",
    "                    if len(result) == 0:\n",
    "                        print(A +\" \"+B+\" \"+C+ '->' + D)\n",
    "                        print()\n",
    "print(count)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.3（练习题）关系R(X, Y, Z)存在3个关系实例：\n",
    "* 关系实例A\n",
    "* 关系实例B，仅比关系实例A少一行（其他行相同）\n",
    "* 关系实例C，仅比关系实例A多一行（其他行相同）\n",
    "\n",
    "创建关系实例A，B和C，要求关系实例A的每个属性都是key，但关系实例B或C的每个属性都不是key，即key至少是两个属性。如果关系实例B或C无法创建，则提供一个空关系实例。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "drop table if exists A;\n",
    "create table A (X int, Y int, Z int);\n",
    "\n",
    "drop table if exists B;\n",
    "\n",
    "drop table if exists C;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5.4（练习题）关系R(X, Y, Z)存在3个关系实例：\n",
    "* 关系实例A\n",
    "* 关系实例B，仅比关系实例A少一行（其他行相同）\n",
    "* 关系实例C，仅比关系实例A多一行（其他行相同）\n",
    "\n",
    "创建关系实例A，B和C，要求关系实例A存在多值依赖Z $\\twoheadrightarrow$ X，但关系实例B和C都不存在该多值依赖。如果关系实例B或C无法创建，则提供一个空关系实例。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%sql\n",
    "drop table if exists A;\n",
    "create table A (X int, Y int, Z int);\n",
    "\n",
    "drop table if exists B;\n",
    "\n",
    "drop table if exists C;\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 作业感想\n",
    "\n",
    "收获:-)，疑惑:-|，吐槽:-(，...，你的反馈很重要"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
